#!/usr/bin/env python
# -*- coding: UTF-8 -*-

from typing import List

from app.dao.user_dao import UserOperateDao
from app.dao.models.mysql_gen import YmUser
from app.types import apiproto
from app import dao
import pandas as pd


def parse_and_save_users(df: pd.DataFrame) -> list[YmUser]:
    # 假设列名为：学号、姓名、专业、身份证号
    required_columns = {"学号", "姓名", "毕业专业", "身份证号"}
    if not required_columns.issubset(df.columns):
        raise ValueError("Excel文件缺少必要列：学号、姓名、毕业专业、身份证号")

    users = []
    for _, row in df.iterrows():
        student_id = str(row["学号"]).strip()
        name = str(row["姓名"]).strip()
        major = str(row["毕业专业"]).strip()
        pid = str(row["身份证号"]).strip()

        if not (student_id and name and major and pid):
            continue

        if "本硕博" in major:
            major = "本硕博贯通班"

        password = pid[-6:] if len(pid) >= 6 else ""

        user = YmUser(
            student_id=student_id,
            name=name,
            major=major,
            pid=pid,
            password=password,
        )
        users.append(user)

    return users


class UserListService:
    """用户列表"""

    @classmethod
    def getUserList(
        cls, queryParam: apiproto.UserListRequest
    ) -> apiproto.UserListResponse:
        """查询用户列表"""

        # 拼凑查询信息
        queryDict = {}
        if queryParam.nick_name != "":
            queryDict["nick_name"] = f"%{queryParam.nick_name}%"
        if queryParam.phone != "":
            queryDict["phone"] = queryParam.phone

        total, result = dao.UserQueryDao.findByPage(
            queryParam.page, queryParam.pageSize, **queryDict
        )
        if total == 0:
            return apiproto.UserListResponse()

        # 格式化数据
        records_list: List[apiproto.UserDetailProto] = []

        for record in result:
            tmp = apiproto.UserDetailProto(
                id=record.id,
                union_id=record.union_id,
                open_id=record.open_id,
                nick_name=record.nick_name,
                avatar=record.avatar,
                phone=record.phone,
                email=record.email,
                last_login=record.last_login,
                status=record.status,
                delete_at=record.delete_at,
                created_at=str(record.created_at),
                updated_at=str(record.updated_at),
            )
            records_list.append(tmp)

        return apiproto.UserListResponse(record_total=total, record_list=records_list)

    @classmethod
    def save_users(cls, dataframe: pd.DataFrame):
        users = parse_and_save_users(dataframe)
        UserOperateDao.saveUserList(users)
        return len(users)

    @staticmethod
    def get_statistics_by_major(major: str):
        return UserOperateDao.get_by_major(major)
